ALTER          proc sp_create_update_test_lot (
@part_number varchar(50), 
@lot_number varchar(50), 
@vendor_lotnumber varchar(50),
@lead_count varchar(50),
@package_type varchar(50),
@STAGENAME varchar(50),
@date_in datetime,
@date_out datetime,
@qty_in int,
@qty_out int,
@Customer_Po varchar(50),
@status varchar(20),
@EXPECTED_DATE_OUT datetime,
@vendor_name varchar(50),
@ps_type varchar(50),
@hold_code varchar(50),  
@hold_reason varchar(50),
@fab_lot_No varchar(50),
@Lot_Category varchar(10),
@Date_code varchar(50),
@Test_program varchar(50)

)
as
begin


	DECLARE @PROCESS_STEP_ID INT
 	DECLARE @LOCATION_ID INT
 	DECLARE @LOT_ID INT
	DECLARE @THIS_LOT_STATUS VARCHAR(50)
	DECLARE @LOT_DATEIN DATETIME
	--setting lot category is production, when category is null
	if @lot_Category is null
	begin
		set @lot_Category = 'PROD'
	end
	
	/* Checking Whether its is a Bom Part Number, if its a part number inserting into inventory lot table as Bom Lot*/
	if @VENDOR_NAME = 'UTAC'
	begin
		select @PROCESS_STEP_ID = PROCESSSTEP_ID FROM PRODUCT WHERE PART_NUMBER = @PART_NUMBER  AND P_Type = 'BOM'
		if @PROCESS_STEP_ID is not null
		begin		
			exec sp_create_update_fg_lot @part_number, @lot_number, @qty_in, @qty_out, @date_in, @date_out, @expected_date_out, 'Finished Goods', @vendor_name, 'Running', @STAGENAME, @Fab_Lot_No, @lot_category
			return
		end
	end
 	/*ended here*/ 
 	 
	SELECT @PROCESS_STEP_ID = PROCESS_STEP.PROCESSSTEP_ID FROM PRODUCT,PROCESS_STEP WHERE PRODUCT.PART_NUMBER = @PART_NUMBER  AND PS_TYPE_NAME = 'Test' AND PS_TYPE = @ps_type AND PRODUCT.PROCESSSTEP_ID=PROCESS_STEP.PROCESSSTEP_ID 
	SELECT @LOCATION_ID = LOCATION_ID FROM LOCATION WHERE L_TYPE = 'TEST' AND L_VENDOR_NAME = @VENDOR_NAME

	IF @PROCESS_STEP_ID IS NULL
 	BEGIN
		SELECT @PROCESS_STEP_ID = PROCESS_STEP.PROCESSSTEP_ID FROM PRODUCT_MAP_TABLE,PROCESS_STEP WHERE PRODUCT_MAP_TABLE.PART_NUMBER = @PART_NUMBER AND PS_TYPE_NAME = 'Test' AND PS_TYPE = @ps_type AND PRODUCT_MAP_TABLE.PROCESSSTEP_ID=PROCESS_STEP.PROCESSSTEP_ID 
 		IF @PROCESS_STEP_ID IS NULL 
 		BEGIN
			--For Discrepancy Lot Notification Alert
			IF ( SELECT COUNT(*) FROM NOTIFICATION_ALERT_TABLE WHERE PART_NUMBER = @PART_NUMBER AND LOT_NUMBER = @LOT_NUMBER AND VENDOR_NAME = @VENDOR_NAME AND PROCESS_TYPE = @ps_type AND IS_USER_NOTIFIED = 1 ) = 0 
			BEGIN
				INSERT INTO Notification_Alert_Table ( Part_Number, Lot_Number, Fab_Lot_Number, Vendor_Name, Process_Type, Notification_Name, Wafer_Qty, Die_Qty, Wafer_id, Status,TimeStamp )
					VALUES ( @part_number, @lot_number, @fab_lot_No, @vendor_name, @ps_type,'Discrepancy Lot Notification', null, @Qty_In, null, 'Open', CURRENT_TIMESTAMP )
			END
				
			 -- DUMMY UPDATE TO AVOID SQLEXCEPTION
 			UPDATE LOT_TABLE SET LOT_NUMBER = 'LOT_NUMBER' WHERE LOT_NUMBER = 'LOT_NUMBER'

 			RETURN
 		END
	END

	SELECT @THIS_LOT_STATUS = LOT_CURRENT_STATUS , @LOT_ID = LOT_ID, @LOT_DATEIN = DATE_IN FROM LOT_TABLE WHERE PROCESSSTEP_ID = @PROCESS_STEP_ID AND LOCATION_ID = @LOCATION_ID AND LOT_NUMBER = @LOT_NUMBER

	IF @DATE_IN IS NULL AND @LOT_DATEIN IS NULL
    BEGIN
        SET @DATE_IN = CONVERT(VARCHAR, CURRENT_TIMESTAMP, 101)
    END
    ELSE
    BEGIN
         IF @DATE_IN IS NULL AND @LOT_DATEIN IS NOT NULL
		 BEGIN	
			SET @DATE_IN = @LOT_DATEIN
		 END
    END
		
	IF @LOT_ID IS NULL -- Check if the lot exists
 	BEGIN 	
		-- Create Lot
 		INSERT INTO LOT_TABLE (PROCESSSTEP_ID, LOCATION_ID, LOT_NUMBER, FAB_LOT_NUMBER, VENDOR_LOT_NUMBER, Wafer_Qty_In, QTY_IN, DATE_IN, DATE_OUT, STAGE_NAME, LOT_CURRENT_STATUS, TIMESTAMP, LOT_ENTERED_BY, LOT_CATEGORY, LOT_DROP_OUT, PO_NUMBER, Lot_Date_Code) 
				VALUES (@PROCESS_STEP_ID, @LOCATION_ID, @LOT_NUMBER, @fab_lot_No, ISNULL(@vendor_lotnumber,@LOT_NUMBER ), null, @QTY_IN, @DATE_IN, @DATE_OUT, @STAGENAME, @STATUS, CURRENT_TIMESTAMP,1, @Lot_Category, 0, @CUSTOMER_PO, @Date_Code )
		SELECT @LOT_ID = @@IDENTITY

		-- For New Lot Notification Alert
		INSERT INTO Notification_Alert_Table ( Part_Number, Lot_Number, Fab_Lot_Number, Vendor_Name, Process_Type, Notification_Name, Wafer_Qty, Die_Qty, Wafer_id, Status, TimeStamp )
		VALUES ( @part_number, @lot_number, @fab_lot_No, @vendor_name, @ps_type,'New Lot Notification', null, @Qty_In, null, 'Open', CURRENT_TIMESTAMP )				
		
		INSERT INTO FINAL_TEST_LOT (LOT_ID, FTL_Expected_Date, FTL_Package_Type,Hold_Code, Hold_Reason, TIMESTAMP, FTL_Test_Program, FTL_Lead_Count)
		VALUES (@LOT_ID, @EXPECTED_DATE_OUT, @package_type, @Hold_Code, @Hold_Reason, CURRENT_TIMESTAMP, @Test_program, @lead_count )

	END
	ELSE
	BEGIN
		UPDATE LOT_TABLE SET LOT_CURRENT_STATUS = @STATUS, QTY_IN = @QTY_IN, DATE_IN = @DATE_IN, STAGE_NAME = @STAGENAME, Lot_Category = @Lot_Category, Lot_Drop_Out = 0, Fab_Lot_Number = @fab_lot_No, Lot_Date_Code = @Date_Code , Vendor_Lot_Number = ISNULL(@vendor_lotnumber,@LOT_NUMBER ) WHERE LOT_ID = @LOT_ID		
		
		UPDATE FINAL_TEST_LOT SET FTL_Expected_Date = @EXPECTED_DATE_OUT, FTL_Package_Type =@package_type, HOLD_CODE = @HOLD_CODE, HOLD_REASON = @HOLD_REASON, TIMESTAMP = CURRENT_TIMESTAMP, FTL_Test_Program = @Test_program, FTL_Lead_Count = @lead_count WHERE LOT_ID = @LOT_ID
	END

	IF @QTY_OUT IS NOT NULL AND  @QTY_OUT > 0
	BEGIN
			UPDATE LOT_TABLE SET LOT_CURRENT_STATUS = 'Close', QTY_OUT = @QTY_OUT, DATE_OUT = @DATE_IN WHERE LOT_ID = @LOT_ID		 
	END

	IF ( (SELECT COUNT(DISTINCT TRT.TEST_REL_ID) FROM TEST_RELEASE_LOT_TABLE TRLT,TEST_RELEASE_TABLE TRT WHERE TRLT.TEST_REL_ID = TRT.TEST_REL_ID and TEST_REL_STATUS = 'APPROVED' 
			AND isnull(SHIPPED_Lot_Number,Lot_Number) = @LOT_NUMBER ) > 0  )
	BEGIN
			UPDATE TEST_RELEASE_TABLE SET TEST_REL_STATUS = 'Closed' WHERE TEST_REL_ID IN ( SELECT DISTINCT TRT.TEST_REL_ID FROM TEST_RELEASE_LOT_TABLE TRLT,TEST_RELEASE_TABLE TRT WHERE TRLT.TEST_REL_ID = TRT.TEST_REL_ID and TEST_REL_STATUS = 'APPROVED' 
			AND isnull(SHIPPED_Lot_Number,Lot_Number) = @LOT_NUMBER )
	END
		
	/*Closing the Previous Transit Lots when ever Lots are came in to Next Process*/
	EXEC SP_CLOSE_PREVIOUSPROCESS_AND_TRANSITLOTS @LOT_NUMBER, @FAB_LOT_NO, @QTY_IN, @DATE_IN
end